/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mb.loadData;


import java.io.StringWriter;
import java.sql.Connection;
import mb.utils.LogText;

import java.sql.*;
import mb.config.Constants;
import mb.config.DatabaseConfig;
import mb.mbmiddle.MbPartMiddle;
import mb.mbpart.async.AysncObject;
import mb.mbpart.memory.BaseMemory;
import mb.mbpart.object.ATMObject;
import mb.mbpart.object.BranchObject;
import mb.mbpart.object.CategoryObject;
import mb.mbpart.object.NewsObject;
import mb.mbpart.object.ProvinceObject;
import mb.mbpart.object.StateObject;
import mb.utils.StringUtils;

/**
 *
 * @author thanh.nguyenchi
 */
public class LoadDataHandler extends Thread {
    LogText objLog;
    DatabaseConfig objConfig;
    MbPartMiddle objMain;
    String newLine;
        
    private Connection conn = null; 
    
    public LoadDataHandler(DatabaseConfig objConfig,LogText objLog, MbPartMiddle objMain) {  
        this.setName(Constants.STR_LOAD_THREAD);
        this.objConfig = objConfig;
        this.objLog = objLog;
        this.objMain = objMain;
        newLine = System.getProperty("line.separator");
        
        initConnection();        
    }
    
    private void initConnection(){
        try{
            // the sql server driver string
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            log("init connection to database");

            // get the sql server database connection
            conn = DriverManager.getConnection(objConfig.getUrl(),objConfig.getUser(),objConfig.getPass());
            if (conn != null){
                log("connect to db successful.");
            }
            else{
                log("can't connect to database");
            }
        }
        catch(SQLException ex){
            log("[LoadDataHandler] initConnection err=" + ex.toString());
        }
        catch(Exception ex) { 
            log("[LoadDataHandler] initConnection err=" + ex.toString());
        } 
    }
    
    private void closeConnection(){
        if (conn != null){
            try{
                conn.close();
            }
            catch(Exception ex){
                
            }            
        }
    }
    
    public void run() {
        try{
            // loading data from SQLServer
            if (conn != null){                
                // load categori & news
                this.loadCatetories();
                // load atm
                this.loadATM();
                // load branches
                this.loadBranches();
                // load promotion
                this.loadPromotions();
                // load service
                //this.loadServices();
                // load companies
                this.loadCompanies();                
                
                // close connection
                closeConnection();
                
                log("End loading data.");
            }  
            else{
                log("Can't load data from database.");
            }
            
        }
        catch(Exception ex){
            log("[LoadDataHandler] err:" + ex.toString());           
        }
        
    }
        
    private void log(String message) {
        objLog.log(message);
    }
    
    public void displayDbProperties(){
        DatabaseMetaData dm = null;
        ResultSet rs = null;
        try{
          if(conn!=null){
              dm = conn.getMetaData();
              System.out.println("Driver Information");
              System.out.println("\tDriver Name: "+ dm.getDriverName());
              System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
              System.out.println("\nDatabase Information ");
              System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
              System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
              System.out.println("Avalilable Catalogs ");
              rs = dm.getCatalogs();
              while(rs.next()){
                  System.out.println("\tcatalog: "+ rs.getString(1));
              } 
              rs.close();
              rs = null;                    
          }
          else System.out.println("Error: No active Connection");
        }catch(Exception e){
          e.printStackTrace();
        }
        dm = null;
     }     

    public void loadATM(){
        log("Load ATM data.....");
        loadProvince(Constants.INT_ATM_TYPE);
    } 
    
    public void loadBranches(){
        log("Load Branches data.....");
        loadProvince(Constants.INT_BRANCH_TYPE);
    }
    
    public void loadServices(){
        log("Load Services data.....");
        String  sResult = loadMenuLevel1(Constants.INT_MENU_LEVEL_1,objConfig.getServices_id());
        
        // add to memory
        BaseMemory objMemory = new BaseMemory(sResult);
        objMain.objMemory.put(Constants.STR_MEM_SERVIES, objMemory);        
    }
    
    public void loadCompanies(){
        log("Load companies data.....");
        String sResult = getSubcompany();
        
        // add to memory
        BaseMemory objMemory = new BaseMemory(sResult);
        objMain.objMemory.put(Constants.STR_MEM_COMPANIES, objMemory);        
    }
    
    public String getCompanyBranch(long id){
        // cong ty con
        Statement stmt = null;
        
        String query =  "DECLARE	@return_value int \n" +
                        "EXEC	@return_value = [spGetCompanyBranch] \n" +
                        "       @Parent_ID = " + id + " \n" +
                        "SELECT	'Return Value' = @return_value";
        
        String sResult = "[";  
        
        try {     
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);            
            while (rs.next()) {                
                BranchObject obj = new BranchObject();
                obj.C_NAME = rs.getString(1);
                obj.C_ADDRESS = rs.getString(2);
                obj.C_LATITUDE = rs.getDouble(3);
                obj.C_LONGITUDE = rs.getDouble(4); 
                obj.C_FAX = rs.getString(5);
                obj.C_PHONE = rs.getString(6);
                obj.C_HOMEPAGE = rs.getString(7);
                obj.C_DESCRIPTION = rs.getString(8);
                obj.C_BRANCH_ID = rs.getInt(9);
                sResult = sResult + obj.toJson() + ",";     
            }
            
            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]";                                      
            
            rs.close();
            stmt.close();
        } catch (Exception ex ) {
            return "[]";
        } 
        
        return sResult;
    }
    public String getSubcompany(){
        // cong ty con
        Statement stmt = null;
            
        String query =  "DECLARE	@return_value int \n" +
                        "EXEC	@return_value = [spGetSubcompany] \n" +                
                        "       @State_ID = " + objConfig.getSubCompany_id() + "\n" + 
                        "SELECT	'Return Value' = @return_value";               
        
        String sResult = "[";  
        
        try {     
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);            
            while (rs.next()) {  
                ProvinceObject obj = new ProvinceObject();
                obj.C_PROVINCE_ID = rs.getInt(1);
                obj.C_NAME = rs.getString(2);
                obj.C_NAME_EN = rs.getString(3);
                obj.C_IMAGE = rs.getString(4);
                obj.C_HOMEPAGE = rs.getString(5);
                //System.out.println(id + "\t" + name + "\t" + text + "\t" + level);                
                // get list state
                
                String branches = getCompanyBranch(obj.C_PROVINCE_ID);
                if (branches.length() > 2){                    
                    sResult = sResult + "{\"name\":\"" + StringUtils.NormalizeJson(obj.C_NAME) + "\"";
                    sResult = sResult + ",\"eng\":\"" + StringUtils.NormalizeJson(obj.C_NAME_EN) + "\"";
                    sResult = sResult + ",\"image\":\"" + StringUtils.NormalizeJson(obj.C_IMAGE) + "\"";
                    sResult = sResult + ",\"homepage\":\"" + StringUtils.NormalizeJson(obj.C_HOMEPAGE) + "\"";
                    sResult = sResult + ",\"branches\":" + branches;
                    sResult = sResult + "}" + ",";                    
                }
                
                                            
            }
            
            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]";                                      
            
            rs.close();
            stmt.close();
        } catch (Exception ex ) {
            return "[]";
        } 
        
        return sResult;
    }
    
    public String loadMenuLevel1(int level, long id){
        Statement stmt = null;            
        String query =  "DECLARE	@return_value int \n" +
                        "EXEC	@return_value = [spLoadServices] \n" +
                        "       @Level = " + level + ", \n" +
                        "       @Parent_ID = " + id + " \n" +
                        "SELECT	'Return Value' = @return_value";                
        
        String sResult = "[";
        
        try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                CategoryObject objCate = new CategoryObject();
                objCate.C_CATEGORY_ID = rs.getInt(1);
                objCate.C_NAME = rs.getString(2);
                String subMenu = loadMenuLevel2(Constants.INT_MENU_LEVEL_2,objCate.C_CATEGORY_ID);
                sResult = sResult + "{\"id\":" + objCate.C_CATEGORY_ID;
                sResult = sResult + ",\"name\":\"" + StringUtils.NormalizeJson(objCate.C_NAME) + "\"";
                sResult = sResult + ",\"data\":" + subMenu;
                sResult = sResult + "},";
               
            }
            
            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]"; 
                        
            
            rs.close();
            stmt.close();
        } catch (Exception ex ) {
            return "[]";
        } 
        return sResult;
    }
    
    public String loadMenuLevel2(int level, long id){
        Statement stmt = null;            
        String query =  "DECLARE	@return_value int \n" +
                        "EXEC	@return_value = [spLoadServices] \n" +
                        "       @Level = " + level + ", \n" +
                        "       @Parent_ID = " + id + " \n" +
                        "SELECT	'Return Value' = @return_value";   
        
        String sResult = "[";
        
        try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                CategoryObject objCate = new CategoryObject();
                objCate.C_CATEGORY_ID = rs.getInt(1);
                objCate.C_NAME = rs.getString(2);
                sResult = sResult + objCate.toJson() + ",";   
                
                // load chi tiet dich vu
                loadServices(objCate.C_CATEGORY_ID);
            }
            
            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]"; 
                                    
            rs.close();
            stmt.close();
        } catch (Exception ex ) {
            return "[]";
        } 
        return sResult;        
    }
   
    public void loadServices(long id){
        log("\t\t Load services id : " + id);
        AysncObject objAsync = new AysncObject(Constants.INT_SERVICE_TYPE, id);
        objConfig.getUpdateQueue().add(objAsync);
        // add list service
        objConfig.addService(id);
        
    }
    public void loadPromotions(){
        log("Load Promotions data.....");
        String sResult = loadNewsById(objConfig.getPromotion_id(),"");
             
        // add to memory
        BaseMemory objMemory = new BaseMemory(sResult);
        objMain.objMemory.put(Constants.STR_MEM_PROMOTIONS, objMemory);
    }
    
    public void loadCatetories(){
        log("Load category data.....");
        Statement stmt = null;
            
        String query = "DECLARE	@return_value int \n" +                   
                       "EXEC	@return_value = [sploadCatetories]\n" +
                       "SELECT	'Return Value' = @return_value";     
        
        String sResult = "[";
        
        try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                CategoryObject objCate = new CategoryObject();
                objCate.C_CATEGORY_ID = rs.getInt(1);
                objCate.C_NAME = rs.getString(2); 
                objCate.C_CATE_IMAGE = rs.getString(3);
                objCate.C_PARENT_ID = rs.getInt(4);
                objCate.C_SCREEN = rs.getString(5);
                objCate.C_SCREEN_DETAIL = rs.getString(6);
                sResult = sResult + objCate.toJson() + ",";
                
                // load detail for C_CATEGORY_ID
                if (objCate.C_PARENT_ID == objConfig.getPreferred_id()){
                    loadCatetoriesById(objCate.C_CATEGORY_ID,objCate.C_NAME);
                }               
                else{
                    loadServices(objCate.C_CATEGORY_ID);
                }
            }
            
            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]";
            
            // add to memory
            BaseMemory objMemory = new BaseMemory(sResult);
            objMain.objMemory.put(Constants.STR_MEM_CATEGORIES, objMemory);
            //System.out.println("out : " + sResult);
            
            rs.close();
            stmt.close();
        } catch (Exception ex ) {
            System.out.println(ex.toString());
        } 
    }
    
    public String loadNewsById(long id,String typeName){
        log("Load news by id :" + id);
        Statement stmt = null;
            
        String query =  "DECLARE	@return_value int \n" +
                        "EXEC	@return_value = [sploadNewsById] \n" +
                        "       @Cate_id = " + id + " \n" +
                        "       SELECT	'Return Value' = @return_value";
               
        
        String sResult = "[";  
        
        try {     
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);            
            while (rs.next()) {                
                NewsObject obj = new NewsObject();
                obj.C_NEWS_ID = rs.getInt(1);
                obj.C_TITLE = rs.getString(2);
                obj.C_TEASER = rs.getString(3);
                obj.C_CONTENT = rs.getString(4);
                obj.C_AVATAR = rs.getString(5);
                obj.C_ADDRESS = rs.getString(6);
                obj.C_LATITUDE = rs.getDouble(7);
                obj.C_LONGITUDE = rs.getDouble(8);
                obj.C_FROM_DATE = rs.getDate(9).toString();
                obj.C_TO_DATE = rs.getDate(10).toString();
                obj.C_PHONE = rs.getString(11);
                obj.C_LINK = rs.getString(12);
                obj.C_TYPE = id; 
                
                sResult = sResult + obj.toJson(id,objConfig) + ",";
            }
            
            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]";

            
            rs.close();
            stmt.close();
        } catch (Exception ex ) {
            return "[]";
        } 
        
        return sResult;
                
    }
    
    public void loadCatetoriesById(long id,String name){
        log("    Load detail for category id = " + id + " name = " + name);
        AysncObject objAsync = new AysncObject(Constants.INT_NEWS_TYPE, id);
        objAsync.setTypeName(name);
        objConfig.getUpdateQueue().add(objAsync);
    }
    
    public void loadProvince(int type){
        Statement stmt = null;
            
        String query =  "DECLARE	@return_value int \n" +
                        "EXEC	@return_value = [sploadProvince] \n" +
                        "SELECT	'Return Value' = @return_value";               
        
        String sResult = "[";  
        
        try {     
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);            
            while (rs.next()) {                
                ProvinceObject obj = new ProvinceObject();
                obj.C_PROVINCE_ID = rs.getInt(1);
                obj.C_NAME = rs.getString(2);
                
                // get list state
                
                String states = getStateByProvince(obj.C_PROVINCE_ID,type);
                if (states.length() > 2){                    
                    sResult = sResult + "{\"city\":\"" + StringUtils.NormalizeJson(obj.C_NAME) + "\"";
                    sResult = sResult + ",\"provinces\":" + states;
                    sResult = sResult + "}" + ",";                    
                }
            }
            
            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]";
                          
            if (type == Constants.INT_ATM_TYPE){
                // add to ATM memory
                BaseMemory objMemory = new BaseMemory(sResult);
                objMain.objMemory.put(Constants.STR_MEM_ATM,objMemory);
            }
            else if(type == Constants.INT_BRANCH_TYPE){                
                // add to Branches memory
                BaseMemory objMemory = new BaseMemory(sResult);
                objMain.objMemory.put(Constants.STR_MEM_BRANCHES,objMemory);
                //System.out.println(sResult);
            }
            else{
                // no 
            }
            //System.out.println("out : " + sResult);
            
            rs.close();
            stmt.close();
        } catch (Exception ex ) {
            System.out.println(ex.toString());
        } 
    }
    
    public String getStateByProvince(long id,int type){
        Statement stmt = null;
        
            
        String query =  "DECLARE	@return_value int \n" +
                        "EXEC	@return_value = [spGetStateByProvince] \n" +
                        "       @Province_id = " + id + " \n" +
                        "SELECT	'Return Value' = @return_value";                
        
        String sResult = "[";  
        
        try {     
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);            
            while (rs.next()) {                
                StateObject obj = new StateObject();
                obj.C_STATE_ID = rs.getInt(1);
                obj.C_NAME = rs.getString(2);
                //sResult = sResult + "\t" + obj.C_STATE_ID + "\t" + obj.C_NAME + newLine;
                String subs = ""; 
                if (type == 1){
                    subs = getListATMByState(obj.C_STATE_ID,obj.C_NAME);
                }
                else if (type == 2){
                    subs = getListBranchesByState(obj.C_STATE_ID,obj.C_NAME);
                }
                else{
                    subs = "";
                }
                if (subs.length() > 2){
                    sResult = sResult + "{\"province\":\"" + StringUtils.NormalizeJson(obj.C_NAME) + "\"";
                    sResult = sResult + ",\"data\":" + subs + "}" + ",";
                }
            }
            
            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]";

            
            rs.close();
            stmt.close();
        } catch (Exception ex ) {
            return "";
        } 
        
        return sResult;
    }
    
    public String getListBranchesByState(long id,String sName){
               Statement stmt = null;

        String query =  "DECLARE	@return_value int \n" +
                        "EXEC	@return_value = [spGetListBranchesByState] \n" +
                        "       @State_ID = " + id + " \n" +
                        "SELECT	'Return Value' = @return_value";                
        
        String sResult = "[";  
        
        try {     
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);            
            while (rs.next()) {                
                BranchObject obj = new BranchObject();
                obj.C_NAME = rs.getString(1);
                obj.C_ADDRESS = rs.getString(2);
                obj.C_LATITUDE = rs.getDouble(3);
                obj.C_LONGITUDE = rs.getDouble(4);
                obj.C_FAX = rs.getString(5);
                obj.C_PHONE = rs.getString(6);
                obj.C_HOMEPAGE = rs.getString(7);
                obj.C_DESCRIPTION = rs.getString(8);
                obj.C_BRANCH_ID = rs.getInt(9);
                sResult = sResult + obj.toJson() + ",";                     
            }

            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]";
            
//            if (sResult.length() > 2){
//                System.out.println(sName + "\t\t" + sResult);
//            }
            
            rs.close();
            stmt.close();
        } catch (Exception ex ) {
            return "[]";
        } 
        
        return sResult;  
    }
    
    public String getListATMByState(long id,String sName){
        Statement stmt = null;

        String query =  "DECLARE	@return_value int \n" +
                        "EXEC	@return_value = [spGetListATMByState] \n " +
                        "       @State_ID = " + id + "\n" +
                        "SELECT	'Return Value' = @return_value ";                
        
        String sResult = "[";  
        
        try {     
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);            
            while (rs.next()) {                
                ATMObject obj = new ATMObject();
                obj.C_NAME = rs.getString(1);
                obj.C_ADDRESS = rs.getString(2);
                obj.C_LATITUDE = rs.getDouble(3);
                obj.C_LONGITUDE = rs.getDouble(4);
                obj.C_HOTLINE = rs.getString(5);
                obj.C_HOMEPAGE = rs.getString(6);
                obj.C_ATM_IMAGE = rs.getString(7);
                obj.C_ATM_ID = rs.getInt(8);
                obj.C_STATUS = rs.getInt(9);
                sResult = sResult + obj.toJson() + ",";                     
            }

            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]";

            rs.close();
            stmt.close();
        } catch (Exception ex ) {
            return "[]";
        } 
        
        return sResult;        
    }
}
